package com.mcp.converter.application.service;

import com.mcp.converter.infrastructure.config.OceanBaseClient;
import com.mcp.converter.infrastructure.config.OracleClient;
import com.mcp.converter.shared.response.ExecutionResult;
import com.mcp.converter.shared.exception.DatabaseMigrationException;
import com.mcp.converter.shared.util.FileSystemUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * 数据库迁移服务
 * 负责Oracle到OceanBase的各种数据库对象迁移
 * 
 * @author zengxiong
 */
@Slf4j
@Service
public class DatabaseMigrationService {

    /**
     * 删除OceanBase临时索引
     */
    @Transactional
    public String dropTempIndexes(String oceanbaseUrl, String oceanbaseUsername, String oceanbasePassword, boolean isAutoExecute) {
        if (StringUtils.isAnyBlank(oceanbaseUrl, oceanbaseUsername, oceanbasePassword)) {
            throw new DatabaseMigrationException("数据库连接参数不能为空");
        }

        try {
            // 创建数据源
            DataSource dataSource = OceanBaseClient.createDataSource(oceanbaseUrl, oceanbaseUsername, oceanbasePassword);
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            // 查询临时索引
            List<String> dropStatements = fetchTempIndexDropStatements(jdbcTemplate);
            if (dropStatements.isEmpty()) {
                return "未找到需要删除的临时索引";
            }

            // 手动模式处理
            if (!isAutoExecute) {
                try {
                    // 确保MCP目录存在
                    Path dirPath = FileSystemUtils.ensureMcpDirectoryExists();

                    // 获取文件路径
                    Path filePath = dirPath.resolve("oceanbase临时索引删除.sql");

                    // 构建SQL文件内容
                    String content = "-- 请在oceanbase数据库中执行以下sql语句\n" + String.join("\n", dropStatements);
                    Files.write(filePath, content.getBytes(StandardCharsets.UTF_8));

                    return String.format("{\"success\": true, \"message\": \"已生成SQL文件: %s\", \"sqlCount\": %d}",
                            filePath.toAbsolutePath().toString(), dropStatements.size());
                } catch (IOException e) {
                    throw new DatabaseMigrationException("文件写入失败: " + e.getMessage(), e);
                }
            }

            // 自动模式 - 执行删除操作
            String result = executeTempIndexDeletion(jdbcTemplate, dropStatements);
            
            log.info("临时索引删除完成，处理了{}个索引", dropStatements.size());
            return result;

        } catch (DatabaseMigrationException e) {
            throw e;
        } catch (Exception e) {
            log.error("处理临时索引失败: {}", e.getMessage(), e);
            throw new DatabaseMigrationException("处理临时索引失败: " + e.getMessage(), e);
        }
    }

    /**
     * Oracle授权迁移
     */
    public String syncGrant(String oracleUrl, String oracleUserName, String oraclePassword,
                           String oracleTableOwner, String oceanbaseUrl, String oceanbaseUsername,
                           String oceanbasePassword, boolean isAutoExecute) {

        if (StringUtils.isAnyBlank(oracleUrl, oracleUserName, oraclePassword, oracleTableOwner,
                oceanbaseUrl, oceanbaseUsername, oceanbasePassword)) {
            throw new DatabaseMigrationException("数据库连接参数不能为空");
        }

        try {
            // 创建数据源
            DataSource oracleDataSource = OracleClient.createDataSource(oracleUrl, oracleUserName, oraclePassword);

            // 查询Oracle中的授权信息
            List<String> grantStatements = fetchGrantStatements(oracleDataSource, oracleTableOwner);
            if (grantStatements.isEmpty()) {
                return "未找到owner为 " + oracleTableOwner + " 的授权信息";
            }

            // 手动模式处理
            if (!isAutoExecute) {
                try {
                    // 确保MCP目录存在
                    Path dirPath = FileSystemUtils.ensureMcpDirectoryExists();

                    // 获取文件路径
                    Path filePath = dirPath.resolve("oracle授权迁移.sql");

                    // 构建SQL文件内容
                    String content = "-- 请在oceanbase数据库中执行以下sql语句\n" + String.join("\n", grantStatements);
                    Files.write(filePath, content.getBytes(StandardCharsets.UTF_8));

                    return String.format("{\"success\": true, \"message\": \"已生成SQL文件: %s\", \"sqlCount\": %d}",
                            filePath.toAbsolutePath(), grantStatements.size());
                } catch (IOException e) {
                    throw new DatabaseMigrationException("文件写入失败: " + e.getMessage(), e);
                }
            }

            // 自动模式 - 在OceanBase中执行授权语句
            DataSource oceanbaseDataSource = OceanBaseClient.createDataSource(oceanbaseUrl, oceanbaseUsername, oceanbasePassword);
            return executeGrants(oceanbaseDataSource, grantStatements);

        } catch (Exception e) {
            log.error("同步授权失败", e);
            throw new DatabaseMigrationException("授权迁移失败: " + e.getMessage(), e);
        }
    }

    /**
     * Oracle序列迁移
     */
    public String syncSequence(String oracleUrl, String oracleUserName, String oraclePassword,
                              String oracleSeqOwner, String oceanbaseUrl, String oceanbaseUsername,
                              String oceanbasePassword, boolean isAutoExecute) {

        if (StringUtils.isAnyBlank(oracleUrl, oracleUserName, oraclePassword, oracleSeqOwner,
                oceanbaseUrl, oceanbaseUsername, oceanbasePassword)) {
            throw new DatabaseMigrationException("数据库连接参数不能为空");
        }

        try {
            // 1. 创建Oracle数据源并获取序列创建语句
            DataSource oracleDataSource = OracleClient.createDataSource(oracleUrl, oracleUserName, oraclePassword);
            List<String> sequenceStatements = fetchSequenceStatements(oracleDataSource, oracleSeqOwner);

            if (sequenceStatements.isEmpty()) {
                return "未找到owner为 " + oracleSeqOwner + " 的序列信息";
            }

            // 2. 根据模式（自动/手动）处理
            if (!isAutoExecute) {
                // 手动模式：生成SQL文件
                try {
                    Path dirPath = FileSystemUtils.ensureMcpDirectoryExists();
                    Path filePath = dirPath.resolve("oracle序列迁移.sql");
                    String content = "-- 请在OceanBase数据库中执行以下SQL语句\n" + String.join("\n", sequenceStatements);
                    Files.write(filePath, content.getBytes(StandardCharsets.UTF_8));

                    return String.format("{\"success\": true, \"message\": \"已生成SQL文件: %s\", \"sqlCount\": %d}",
                            filePath.toAbsolutePath(), sequenceStatements.size());
                } catch (IOException e) {
                    throw new DatabaseMigrationException("文件写入失败: " + e.getMessage(), e);
                }
            } else {
                // 自动模式：在OceanBase中执行
                DataSource oceanbaseDataSource = OceanBaseClient.createDataSource(oceanbaseUrl, oceanbaseUsername, oceanbasePassword);
                return executeSequenceCreation(oceanbaseDataSource, sequenceStatements);
            }

        } catch (Exception e) {
            log.error("同步序列失败", e);
            throw new DatabaseMigrationException("序列迁移失败: " + e.getMessage(), e);
        }
    }

    /**
     * Oracle同义词迁移
     */
    public String syncPublicSynonym(String oracleUrl, String oracleUserName, String oraclePassword,
                                   String oracleTableOwner, String oceanbaseUrl, String oceanbaseUsername,
                                   String oceanbasePassword, boolean isAutoExecute) {

        if (StringUtils.isAnyBlank(oracleUrl, oracleUserName, oraclePassword, oracleTableOwner,
                oceanbaseUrl, oceanbaseUsername, oceanbasePassword)) {
            throw new DatabaseMigrationException("数据库连接参数不能为空");
        }

        try {
            // 1. 创建Oracle数据源并获取同义词创建语句
            DataSource oracleDataSource = OracleClient.createDataSource(oracleUrl, oracleUserName, oraclePassword);
            List<String> synonymStatements = fetchSynonymStatements(oracleDataSource, oracleTableOwner);

            if (synonymStatements.isEmpty()) {
                return "未找到owner为 " + oracleTableOwner + " 的同义词信息";
            }

            // 2. 根据模式（自动/手动）处理
            if (!isAutoExecute) {
                // 手动模式：生成SQL文件
                try {
                    Path dirPath = FileSystemUtils.ensureMcpDirectoryExists();
                    Path filePath = dirPath.resolve("oracle同义词迁移.sql");
                    String content = "-- 请在OceanBase数据库中执行以下SQL语句\n" + String.join("\n", synonymStatements);
                    Files.write(filePath, content.getBytes(StandardCharsets.UTF_8));

                    return String.format("{\"success\": true, \"message\": \"已生成SQL文件: %s\", \"sqlCount\": %d}",
                            filePath.toAbsolutePath().toString(), synonymStatements.size());
                } catch (IOException e) {
                    throw new DatabaseMigrationException("文件写入失败: " + e.getMessage(), e);
                }
            }

            // 自动模式：在OceanBase中执行
            DataSource oceanbaseDataSource = OceanBaseClient.createDataSource(oceanbaseUrl, oceanbaseUsername, oceanbasePassword);
            return executeSynonymCreation(oceanbaseDataSource, synonymStatements);

        } catch (Exception e) {
            log.error("同步同义词失败", e);
            throw new DatabaseMigrationException("同义词迁移失败: " + e.getMessage(), e);
        }
    }

    /**
     * Oracle存储过程迁移
     */
    public String syncProcedure(String oracleUrl, String oracleUserName, String oraclePassword,
                               String oracleProcedureOwner, String oceanbaseUrl, String oceanbaseUsername,
                               String oceanbasePassword, boolean isAutoExecute) {

        if (StringUtils.isAnyBlank(oracleUrl, oracleUserName, oraclePassword, oracleProcedureOwner,
                oceanbaseUrl, oceanbaseUsername, oceanbasePassword)) {
            throw new DatabaseMigrationException("数据库连接参数不能为空");
        }

        try {
            // 1. 创建Oracle数据源并获取存储过程创建语句
            DataSource oracleDataSource = OracleClient.createDataSource(oracleUrl, oracleUserName, oraclePassword);
            List<String> procedureStatements = fetchProcedureStatements(oracleDataSource, oracleProcedureOwner);

            if (procedureStatements.isEmpty()) {
                return "未找到owner为 " + oracleProcedureOwner + " 的存储过程信息";
            }

            // 2. 根据模式（自动/手动）处理
            if (!isAutoExecute) {
                // 手动模式：生成SQL文件
                try {
                    Path dirPath = FileSystemUtils.ensureMcpDirectoryExists();
                    Path filePath = dirPath.resolve("oracle存储过程迁移.sql");
                    
                    // 构建文件内容，确保格式正确
                    StringBuilder contentBuilder = new StringBuilder();
                    contentBuilder.append("-- 请在OceanBase数据库中执行以下SQL语句\n\n");
                    
                    for (int i = 0; i < procedureStatements.size(); i++) {
                        contentBuilder.append(procedureStatements.get(i));
                        // 只在不是最后一个存储过程时添加Oracle/OceanBase标准的存储过程分隔符
                        if (i < procedureStatements.size() - 1) {
                            contentBuilder.append("\n/\n\n");
                        }
                    }
                    
                    Files.write(filePath, contentBuilder.toString().getBytes(StandardCharsets.UTF_8));

                    return String.format("{\"success\": true, \"message\": \"已生成SQL文件: %s\", \"sqlCount\": %d}",
                            filePath.toAbsolutePath(), procedureStatements.size());
                } catch (IOException e) {
                    throw new DatabaseMigrationException("文件写入失败: " + e.getMessage(), e);
                }
            }

            // 自动模式：在OceanBase中执行
            DataSource oceanbaseDataSource = OceanBaseClient.createDataSource(oceanbaseUrl, oceanbaseUsername, oceanbasePassword);
            return executeProcedureCreation(oceanbaseDataSource, procedureStatements);

        } catch (Exception e) {
            log.error("同步存储过程失败", e);
            throw new DatabaseMigrationException("存储过程迁移失败: " + e.getMessage(), e);
        }
    }

    /**
     * Oracle函数迁移
     */
    public String syncFunction(String oracleUrl, String oracleUserName, String oraclePassword,
                              String oracleFunctionOwner, String oceanbaseUrl, String oceanbaseUsername,
                              String oceanbasePassword, boolean isAutoExecute) {

        if (StringUtils.isAnyBlank(oracleUrl, oracleUserName, oraclePassword, oracleFunctionOwner,
                oceanbaseUrl, oceanbaseUsername, oceanbasePassword)) {
            throw new DatabaseMigrationException("数据库连接参数不能为空");
        }

        try {
            // 1. 创建Oracle数据源并获取函数创建语句
            DataSource oracleDataSource = OracleClient.createDataSource(oracleUrl, oracleUserName, oraclePassword);
            List<String> functionStatements = fetchFunctionStatements(oracleDataSource, oracleFunctionOwner);

            if (functionStatements.isEmpty()) {
                return "未找到owner为 " + oracleFunctionOwner + " 的函数信息";
            }

            // 2. 根据模式（自动/手动）处理
            if (!isAutoExecute) {
                // 手动模式：生成SQL文件
                try {
                    Path dirPath = FileSystemUtils.ensureMcpDirectoryExists();
                    Path filePath = dirPath.resolve("oracle函数迁移.sql");
                    
                    // 构建文件内容，确保格式正确
                    StringBuilder contentBuilder = new StringBuilder();
                    contentBuilder.append("-- 请在OceanBase数据库中执行以下SQL语句\n\n");
                    
                    for (int i = 0; i < functionStatements.size(); i++) {
                        contentBuilder.append(functionStatements.get(i));
                        // 只在不是最后一个函数时添加Oracle/OceanBase标准的函数分隔符
                        if (i < functionStatements.size() - 1) {
                            contentBuilder.append("\n/\n\n");
                        }
                    }
                    
                    Files.write(filePath, contentBuilder.toString().getBytes(StandardCharsets.UTF_8));

                    return String.format("{\"success\": true, \"message\": \"已生成SQL文件: %s\", \"sqlCount\": %d}",
                            filePath.toAbsolutePath(), functionStatements.size());
                } catch (IOException e) {
                    throw new DatabaseMigrationException("文件写入失败: " + e.getMessage(), e);
                }
            }

            // 自动模式：在OceanBase中执行
            DataSource oceanbaseDataSource = OceanBaseClient.createDataSource(oceanbaseUrl, oceanbaseUsername, oceanbasePassword);
            return executeFunctionCreation(oceanbaseDataSource, functionStatements);

        } catch (Exception e) {
            log.error("同步函数失败", e);
            throw new DatabaseMigrationException("函数迁移失败: " + e.getMessage(), e);
        }
    }

    /**
     * 获取临时索引删除语句
     */
    private List<String> fetchTempIndexDropStatements(JdbcTemplate jdbcTemplate) {
        String sql = buildTempIndexQuerySql();
        try {
            return jdbcTemplate.queryForList(sql, String.class);
        } catch (Exception e) {
            log.error("查询临时索引失败", e);
            return Collections.emptyList();
        }
    }

    /**
     * 构建临时索引查询SQL
     */
    private String buildTempIndexQuerySql() {
        return "SELECT 'DROP INDEX UK_' || t.table_name || '_OMS_ROWID;' FROM user_tables t " +
                "WHERE EXISTS (SELECT 1 FROM all_indexes i WHERE i.index_name = 'UK_' || t.table_name || '_OMS_ROWID')";
    }

    /**
     * 执行临时索引删除
     */
    private String executeTempIndexDeletion(JdbcTemplate jdbcTemplate, List<String> dropStatements) {
        List<String> successDrops = new ArrayList<>();
        List<String> failedDrops = new ArrayList<>();

        for (String dropSql : dropStatements) {
            try {
                jdbcTemplate.execute(dropSql);
                successDrops.add(dropSql.replace("DROP INDEX ", "").replace(";", ""));
                log.debug("索引删除成功: {}", dropSql);
            } catch (Exception e) {
                String errorMsg = dropSql.replace("DROP INDEX ", "").replace(";", "") + " - 错误: " + e.getMessage();
                failedDrops.add(errorMsg);
                log.warn("索引删除失败: {}", errorMsg);
            }
        }

        return buildTempIndexDeletionResult(successDrops, failedDrops);
    }

    /**
     * 构建临时索引删除结果
     */
    private String buildTempIndexDeletionResult(List<String> successDrops, List<String> failedDrops) {
        StringBuilder result = new StringBuilder();
        result.append(String.format("成功删除%d个索引:\n", successDrops.size()));
        successDrops.forEach(index -> result.append(index).append("\n"));

        if (!failedDrops.isEmpty()) {
            result.append("\n删除失败").append(failedDrops.size()).append("个索引:\n");
            failedDrops.forEach(index -> result.append(index).append("\n"));
        }

        return result.toString();
    }

    /**
     * 获取Oracle授权语句
     */
    private List<String> fetchGrantStatements(DataSource oracleDataSource, String oracleTableOwner) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oracleDataSource);
        String sql = buildGrantQuerySql();

        try {
            return jdbcTemplate.queryForList(sql, String.class, oracleTableOwner);
        } catch (Exception e) {
            log.error("查询Oracle授权信息失败", e);
            return Collections.emptyList();
        }
    }

    /**
     * 构建授权查询SQL
     */
    private String buildGrantQuerySql() {
        return "SELECT 'GRANT ' || LISTAGG(privilege, ', ') WITHIN GROUP (ORDER BY privilege) || " +
               "' ON ' || owner || '.' || table_name || ' TO ' || grantee || ';' " +
               "FROM dba_tab_privs " +
               "WHERE owner = ? " +
               "AND table_name NOT LIKE 'BIN$%' " +
               "GROUP BY owner, table_name, grantee";
    }

    /**
     * 执行授权语句
     */
    private String executeGrants(DataSource oceanbaseDataSource, List<String> grantStatements) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oceanbaseDataSource);
        ExecutionResult result = new ExecutionResult();

        for (String grantSql : grantStatements) {
            try {
                jdbcTemplate.execute(grantSql);
                result.addSuccess(grantSql);
                log.info("授权成功: {}", grantSql);
            } catch (Exception e) {
                String errorMsg = grantSql + " - 错误: " + e.getMessage();
                result.addFailure(errorMsg);
                log.error("授权失败: {}", errorMsg, e);
            }
        }

        return buildGrantExecutionResult(grantStatements.size(), result);
    }

    /**
     * 构建授权执行结果
     */
    private String buildGrantExecutionResult(int totalCount, ExecutionResult result) {
        StringBuilder sb = new StringBuilder();
        sb.append("在Oracle中找到授权语句: ").append(totalCount).append("条\n");
        sb.append("成功同步到OceanBase: ").append(result.getSuccessCount()).append("条\n");
        sb.append("同步失败: ").append(result.getFailureCount()).append("条\n\n");

        if (!result.getSuccessItems().isEmpty()) {
            sb.append("成功的授权:\n");
            result.getSuccessItems().forEach(grant -> sb.append(grant).append("\n"));
            sb.append("\n");
        }

        if (!result.getFailureItems().isEmpty()) {
            sb.append("失败的授权:\n");
            result.getFailureItems().forEach(grant -> sb.append(grant).append("\n"));
        }

        return sb.toString();
    }

    /**
     * 获取Oracle序列语句
     */
    private List<String> fetchSequenceStatements(DataSource oracleDataSource, String oracleSeqOwner) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oracleDataSource);
        String sql = buildSequenceQuerySql();

        try {
            return jdbcTemplate.queryForList(sql, String.class, oracleSeqOwner);
        } catch (Exception e) {
            log.error("查询Oracle序列信息失败", e);
            return Collections.emptyList();
        }
    }

    /**
     * 构建序列查询SQL
     */
    private String buildSequenceQuerySql() {
        return "SELECT 'CREATE SEQUENCE ' || sequence_name || " +
               "' START WITH ' || (last_number + cache_size) || " +
               "' INCREMENT BY ' || increment_by || " +
               "' MINVALUE ' || min_value || " +
               "' MAXVALUE ' || max_value || " +
               "' ' || CASE WHEN cycle_flag = 'Y' THEN 'CYCLE' ELSE 'NOCYCLE' END || " +
               "' CACHE ' || cache_size || ';' AS create_seq_sql " +
               "FROM all_sequences WHERE sequence_owner = ?";
    }

    /**
     * 执行序列创建
     */
    private String executeSequenceCreation(DataSource oceanbaseDataSource, List<String> sequenceStatements) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oceanbaseDataSource);
        ExecutionResult result = new ExecutionResult();

        for (String sequenceSql : sequenceStatements) {
            try {
                jdbcTemplate.execute(sequenceSql);
                result.addSuccess(sequenceSql);
                log.info("序列创建成功: {}", sequenceSql);
            } catch (Exception e) {
                String errorMsg = sequenceSql + " - 错误: " + e.getMessage();
                result.addFailure(errorMsg);
                log.error("序列创建失败: {}", errorMsg, e);
            }
        }

        return buildSequenceExecutionResult(sequenceStatements.size(), result);
    }

    /**
     * 构建序列执行结果
     */
    private String buildSequenceExecutionResult(int totalCount, ExecutionResult result) {
        StringBuilder sb = new StringBuilder();
        sb.append("在Oracle中找到序列: ").append(totalCount).append("个\n");
        sb.append("成功迁移到OceanBase: ").append(result.getSuccessCount()).append("个\n");
        sb.append("迁移失败: ").append(result.getFailureCount()).append("个\n\n");

        if (!result.getSuccessItems().isEmpty()) {
            sb.append("成功创建的序列:\n");
            result.getSuccessItems().forEach(seq -> sb.append(seq).append("\n"));
            sb.append("\n");
        }

        if (!result.getFailureItems().isEmpty()) {
            sb.append("创建失败的序列:\n");
            result.getFailureItems().forEach(seq -> sb.append(seq).append("\n"));
        }

        return sb.toString();
    }

    /**
     * 获取Oracle同义词语句
     */
    private List<String> fetchSynonymStatements(DataSource oracleDataSource, String oracleTableOwner) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oracleDataSource);
        String sql = "SELECT 'CREATE OR REPLACE PUBLIC SYNONYM ' || synonym_name || ' FOR ' || table_owner || '.' || " +
                     "table_name || ';' " +
                     "FROM dba_synonyms WHERE table_owner = ? ";

        try {
            return jdbcTemplate.queryForList(sql, String.class, oracleTableOwner);
        } catch (Exception e) {
            log.error("查询Oracle同义词信息失败", e);
            return Collections.emptyList();
        }
    }

    /**
     * 执行同义词创建
     */
    private String executeSynonymCreation(DataSource oceanbaseDataSource, List<String> synonymStatements) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oceanbaseDataSource);
        ExecutionResult result = new ExecutionResult();

        for (String synonymSql : synonymStatements) {
            try {
                jdbcTemplate.execute(synonymSql);
                result.addSuccess(synonymSql);
                log.info("同义词创建成功: {}", synonymSql);
            } catch (Exception e) {
                String errorMsg = synonymSql + " - 错误: " + e.getMessage();
                result.addFailure(errorMsg);
                log.error("同义词创建失败: {}", errorMsg, e);
            }
        }

        return buildSynonymExecutionResult(synonymStatements.size(), result);
    }

    /**
     * 构建同义词执行结果
     */
    private String buildSynonymExecutionResult(int totalCount, ExecutionResult result) {
        StringBuilder sb = new StringBuilder();
        sb.append("在Oracle中找到同义词: ").append(totalCount).append("个\n");
        sb.append("成功迁移到OceanBase: ").append(result.getSuccessCount()).append("个\n");
        sb.append("迁移失败: ").append(result.getFailureCount()).append("个\n\n");

        if (!result.getSuccessItems().isEmpty()) {
            sb.append("成功创建的同义词:\n");
            result.getSuccessItems().forEach(syn -> sb.append(syn).append("\n"));
            sb.append("\n");
        }

        if (!result.getFailureItems().isEmpty()) {
            sb.append("创建失败的同义词:\n");
            result.getFailureItems().forEach(syn -> sb.append(syn).append("\n"));
        }

        return sb.toString();
    }

    /**
     * 获取Oracle存储过程语句
     */
    private List<String> fetchProcedureStatements(DataSource oracleDataSource, String oracleProcedureOwner) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oracleDataSource);
        
        try {
            // 第一步：获取所有存储过程名称
            List<String> procedureNames = fetchProcedureNames(jdbcTemplate, oracleProcedureOwner);
            if (procedureNames.isEmpty()) {
                return Collections.emptyList();
            }
            
            // 第二步：根据存储过程名称获取DDL语句
            List<String> procedureStatements = new ArrayList<>();
            for (String procedureName : procedureNames) {
                String ddl = fetchProcedureDDL(jdbcTemplate, procedureName, oracleProcedureOwner);
                if (ddl != null && !ddl.trim().isEmpty()) {
                    procedureStatements.add(ddl);
                }
            }
            
            return procedureStatements;
        } catch (Exception e) {
            log.error("查询Oracle存储过程信息失败", e);
            return Collections.emptyList();
        }
    }
    
    /**
     * 获取存储过程名称列表
     */
    private List<String> fetchProcedureNames(JdbcTemplate jdbcTemplate, String oracleProcedureOwner) {
        String sql = "SELECT name FROM dba_source WHERE owner = ? AND type = 'PROCEDURE' GROUP BY name ORDER BY name";
        try {
            return jdbcTemplate.queryForList(sql, String.class, oracleProcedureOwner);
        } catch (Exception e) {
            log.error("查询存储过程名称失败", e);
            return Collections.emptyList();
        }
    }
    
    /**
     * 根据存储过程名称获取DDL语句
     * 使用逐行查询避免LISTAGG的4000字符限制
     */
    private String fetchProcedureDDL(JdbcTemplate jdbcTemplate, String procedureName, String oracleProcedureOwner) {
        String sql = "SELECT CASE WHEN line = 1 THEN 'CREATE ' || text ELSE text END AS text_line FROM dba_source WHERE name = ? AND owner = ? AND type = 'PROCEDURE' ORDER BY line";
        try {
            List<String> lines = jdbcTemplate.queryForList(sql, String.class, procedureName, oracleProcedureOwner);
            if (lines.isEmpty()) {
                return null;
            }
            
            // 手动拼接所有行，避免LISTAGG的字符限制
            StringBuilder ddlBuilder = new StringBuilder();
            for (String line : lines) {
                if (line != null) {
                    ddlBuilder.append(line).append("\n");
                }
            }
            
            String ddl = ddlBuilder.toString().trim();
            if (!ddl.isEmpty()) {
                // 确保DDL以分号结尾
                if (!ddl.endsWith(";")) {
                    ddl += ";";
                }
            }
            return ddl;
        } catch (Exception e) {
            log.error("查询存储过程DDL失败: {}", procedureName, e);
            return null;
        }
    }

    /**
     * 执行存储过程创建
     */
    private String executeProcedureCreation(DataSource oceanbaseDataSource, List<String> procedureStatements) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oceanbaseDataSource);
        ExecutionResult result = new ExecutionResult();

        for (String procedureSql : procedureStatements) {
            try {
                jdbcTemplate.execute(procedureSql);
                result.addSuccess(procedureSql);
                log.info("存储过程创建成功: {}", procedureSql);
            } catch (Exception e) {
                String errorMsg = procedureSql + " - 错误: " + e.getMessage();
                result.addFailure(errorMsg);
                log.error("存储过程创建失败: {}", errorMsg, e);
            }
        }

        return buildProcedureExecutionResult(procedureStatements.size(), result);
    }

    /**
    /**
     * 构建存储过程执行结果
     */
    private String buildProcedureExecutionResult(int totalCount, ExecutionResult result) {
        StringBuilder sb = new StringBuilder();
        sb.append("在Oracle中找到存储过程: ").append(totalCount).append("个\n");
        sb.append("成功迁移到OceanBase: ").append(result.getSuccessCount()).append("个\n");
        sb.append("迁移失败: ").append(result.getFailureCount()).append("个\n\n");

        if (!result.getSuccessItems().isEmpty()) {
            sb.append("成功创建的存储过程:\n");
            result.getSuccessItems().forEach(proc -> sb.append(proc).append("\n"));
            sb.append("\n");
        }

        if (!result.getFailureItems().isEmpty()) {
            sb.append("创建失败的存储过程:\n");
            result.getFailureItems().forEach(proc -> sb.append(proc).append("\n"));
        }

        return sb.toString();
    }

    /**
     * 获取Oracle函数语句
     */
    private List<String> fetchFunctionStatements(DataSource oracleDataSource, String oracleFunctionOwner) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oracleDataSource);
        
        try {
            // 第一步：获取所有函数名称
            List<String> functionNames = fetchFunctionNames(jdbcTemplate, oracleFunctionOwner);
            if (functionNames.isEmpty()) {
                return Collections.emptyList();
            }
            
            // 第二步：根据函数名称获取DDL语句
            List<String> functionStatements = new ArrayList<>();
            for (String functionName : functionNames) {
                String ddl = fetchFunctionDDL(jdbcTemplate, functionName, oracleFunctionOwner);
                if (ddl != null && !ddl.trim().isEmpty()) {
                    functionStatements.add(ddl);
                }
            }
            
            return functionStatements;
        } catch (Exception e) {
            log.error("查询Oracle函数信息失败", e);
            return Collections.emptyList();
        }
    }
    
    /**
     * 获取函数名称列表
     */
    private List<String> fetchFunctionNames(JdbcTemplate jdbcTemplate, String oracleFunctionOwner) {
        String sql = "SELECT NAME FROM ALL_SOURCE WHERE TYPE = 'FUNCTION' AND OWNER = ? GROUP BY NAME ORDER BY NAME";
        try {
            return jdbcTemplate.queryForList(sql, String.class, oracleFunctionOwner);
        } catch (Exception e) {
            log.error("查询函数名称失败", e);
            return Collections.emptyList();
        }
    }
    
    /**
     * 根据函数名称获取DDL语句
     * 使用逐行查询避免LISTAGG的4000字符限制
     */
    private String fetchFunctionDDL(JdbcTemplate jdbcTemplate, String functionName, String oracleFunctionOwner) {
        String sql = "SELECT CASE WHEN line = 1 THEN 'CREATE OR REPLACE ' || text ELSE text END AS text_line FROM " +
                "ALL_SOURCE WHERE name = ? AND owner = ? AND type = 'FUNCTION' ORDER BY line";
        try {
            List<String> lines = jdbcTemplate.queryForList(sql, String.class, functionName, oracleFunctionOwner);
            if (lines.isEmpty()) {
                return null;
            }
            
            // 手动拼接所有行，避免LISTAGG的字符限制
            StringBuilder ddlBuilder = new StringBuilder();
            for (String line : lines) {
                if (line != null) {
                    ddlBuilder.append(line).append("\n");
                }
            }
            
            String ddl = ddlBuilder.toString().trim();
            if (!ddl.isEmpty()) {
                // 确保DDL以分号结尾
                if (!ddl.endsWith(";")) {
                    ddl += ";";
                }
            }
            return ddl;
        } catch (Exception e) {
            log.error("查询函数DDL失败: {}", functionName, e);
            return null;
        }
    }

    /**
     * 执行函数创建
     */
    private String executeFunctionCreation(DataSource oceanbaseDataSource, List<String> functionStatements) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(oceanbaseDataSource);
        ExecutionResult result = new ExecutionResult();

        for (String functionSql : functionStatements) {
            try {
                jdbcTemplate.execute(functionSql);
                result.addSuccess(functionSql);
                log.info("函数创建成功: {}", functionSql);
            } catch (Exception e) {
                String errorMsg = functionSql + " - 错误: " + e.getMessage();
                result.addFailure(errorMsg);
                log.error("函数创建失败: {}", errorMsg, e);
            }
        }

        return buildFunctionExecutionResult(functionStatements.size(), result);
    }

    /**
     * 构建函数执行结果
     */
    private String buildFunctionExecutionResult(int totalCount, ExecutionResult result) {
        StringBuilder sb = new StringBuilder();
        sb.append("在Oracle中找到函数: ").append(totalCount).append("个\n");
        sb.append("成功迁移到OceanBase: ").append(result.getSuccessCount()).append("个\n");
        sb.append("迁移失败: ").append(result.getFailureCount()).append("个\n\n");

        if (!result.getSuccessItems().isEmpty()) {
            sb.append("成功创建的函数:\n");
            result.getSuccessItems().forEach(func -> sb.append(func).append("\n"));
            sb.append("\n");
        }

        if (!result.getFailureItems().isEmpty()) {
            sb.append("创建失败的函数:\n");
            result.getFailureItems().forEach(func -> sb.append(func).append("\n"));
        }

        return sb.toString();
    }
}
